﻿CREATE PROCEDURE [dbo].[s_PaymentInsert]
	@PaymentID int
AS 
SET NOCOUNT ON
PRINT OBJECT_NAME(@@PROCID)+':Processing - '+STR(@PaymentID)

DECLARE @LoanID int,@NewPayment money, @DatePaid smalldatetime,@StatusID int
DECLARE @PrincipalPart numeric(18,2),@InterestPart numeric(18,2),@LateFeePart numeric(18,2),@NSFFeePart numeric(18,2)
SELECT @LoanID = P.LoanID ,@NewPayment = P.Amount, @DatePaid = P.DatePaid,
@PrincipalPart = PINP.PrincipalPart,@InterestPart = PINP.InterestPart,@LateFeePart = PINP.LateFeePart,@NSFFeePart = PINP.NSFFeePart,
@StatusID = ISNULL(StatusID,[dbo].[fs_StateID_Start]('t_Payment'))
FROM t_Payment P INNER JOIN t_PaymentInput PINP ON P.ID = PINP.PaymentID
WHERE P.ID = @PaymentID

SELECT * FROM t_PaymentInput WHERE PaymentID = @PaymentID

DECLARE @RecalcPrinBalParamID int SET @RecalcPrinBalParamID = dbo.fs_StateParamID__RecalcPrinBal()

DECLARE @PrincBal money,@DateNext smalldatetime,@PrevDatePaid smalldatetime

DECLARE @CI int EXEC @CI = sp__Context_SecurityOff 1
UPDATE t_Payment SET IsProcessed = 0 WHERE LoanID = @LoanID AND DatePaid > @DatePaid
DELETE FROM t_PaymentTransaction WHERE PaymentID = @PaymentID
EXEC sp__Context_SecurityOff @CI
SELECT 'I' I,* FROM t_Payment WHERE ID = @PaymentID

IF dbo.fs_StateParams_IsRecalcPrinBal(@StatusID) = 1 BEGIN

SELECT TOP 1 @PrincBal = P.PrincipalBalance,@DateNext = P.DateNext,@PrevDatePaid = P.DatePaid
FROM t_Payment P 
WHERE P.LoanID = @LoanID AND IsProcessed = 1 AND dbo.fs_StateParamValue(StatusID,@RecalcPrinBalParamID) = 1
ORDER BY P.DatePaid DESC, P.ID DESC

DECLARE @PaymentNumber numeric(5,2)
SELECT @PaymentNumber = ISNULL(MAX(PaymentNumber),0) FROM t_Payment P
WHERE P.LoanID = @LoanID AND IsProcessed = 1 AND dbo.fs_StateParams_IsRecalcPrinBal(StatusID) = 1

DECLARE @Rate numeric(9,9),@ToLateFee money,@FirstPmtDate datetime,@Payment money,
				@APR numeric(10,10),@DayCountConvention tinyint,@GraceDays int
SELECT @APR = LI.APR/100.00000000,
			 @Rate = LI.APR/LI.PaymentsPerYear/100,
			 @PrincBal = ISNULL(@PrincBal,LI.AmountFinanced),
			 @FirstPmtDate = LI.FirstPaymentDate,
			 @DateNext = ISNULL(@DateNext,LI.FirstPaymentDate),
			 @PrevDatePaid = ISNULL(@PrevDatePaid,LI.ContractDate),
			 @Payment = LI.Payment,
			 @DayCountConvention = LI.DayCountConventionID
FROM v_TruthInLending LI WHERE @LoanID = LI.LoanID

SELECT			 @ToLateFee = LCI.Charge,@GraceDays = ISNULL(SLC.[Late Days],PS.LateFeeGrace)
FROM v_TruthInLending LI
INNER JOIN t_Loan_Borrower L_B ON L_B.LoanID = LI.LoanID AND L_B.Pos = 0
INNER JOIN t_Address A ON L_B.BorrowerID = A.BorrowerID AND A.Pos = 0
LEFT OUTER JOIN t_StateLateCharge SLC ON SLC.State = A.StateCode
INNER JOIN ts_LoanPaymentType LPT ON LI.PaymentTypeID = LPT.ID
CROSS JOIN t_PaymentSetting PS
CROSS APPLY LateChargeInfo(LI.PrincipalBalance,LI.Payment,LI.PaymentsPerYear,@DateNext,@DatePaid,
	ISNULL(SLC.[Late Days],PS.LateFeeGrace),ISNULL(SLC.[Late Chg%],PS.LateFeePerc),ISNULL(SLC.[Late Chg Max],PS.LateFeeMax))LCI
WHERE LI.LoanID = @LoanID

DECLARE @ToInterest money
SET @ToInterest = CASE WHEN NOT @InterestPart IS NULL THEN @InterestPart
											 ELSE dbo.ACCRINTM(@PrevDatePaid,@DatePaid,@APR,@PrincBal,@DayCountConvention) END
PRINT '@ToInterest:'+str(@ToInterest)+':'+STR(dbo.ACCRINTM(@PrevDatePaid,@DatePaid,@APR,@PrincBal,@DayCountConvention))

--SELECT 'PEB'T,* FROM PaymentEscrowBalance(@LoanID)
DECLARE @OwnedInterest money,@OwnedLateFee money,@OwnedNSF money
SELECT @OwnedInterest = 0,@OwnedLateFee  = 0,@OwnedNSF = 0
SELECT @OwnedInterest = Interest,@OwnedLateFee = LateFee,@OwnedNSF = NSF FROM PaymentEscrowBalance(@LoanID)
--SELECT @OwnedInterest OwnedInterest,@OwnedLateFee OwnedLateFee,@OwnedNSF OwnedNSF

DECLARE @WF TABLE(ToOwnedInterest money,InterestPart money,AddEscrowInterest money,PrincipalPart money,ToOwnedLateFee money,
									LateFeePart money,AddEscrowLateFee money,ToOwnedNSF money,NSFPart money,AddEscrowNSF money,TranBalance money)

INSERT INTO @WF
SELECT WF.* 
FROM WaterFall (@Payment,@NewPayment-ISNULL(@LateFeePart,0),@ToInterest,
								CASE WHEN @LateFeePart IS NULL THEN @ToLateFee ELSE 0 END,0,@OwnedInterest,
								@OwnedLateFee,@OwnedNSF) WF
SELECT 'WF' T,* FROM @WF
IF NOT @LateFeePart IS NULL
	UPDATE @WF SET LateFeePart = @LateFeePart,AddEscrowLateFee = AddEscrowLateFee+(@LateFeePart-@ToLateFee)

DECLARE @NewDateNext smalldatetime,@ExpectedBalance money,@ExpectedPayment smallint
SELECT @PaymentNumber = @PaymentNumber + @NewPayment/ @Payment

SELECT @NewDateNext = Date FROM PaymentScheduleByLoanID(@LoanID) CROSS JOIN t_PaymentSetting PS
WHERE PaymentNumber = FLOOR(@PaymentNumber+PS.AmountTolerance)

SELECT @ExpectedBalance = RemainingPrinciple, @ExpectedPayment = PaymentNumber
FROM PaymentScheduleByLoanID(@LoanID) WHERE Date <= @DatePaid ORDER BY PaymentNumber


UPDATE P SET
	P.DateScheduled = @DateNext,P.DateNext = @NewDateNext,
	P.Interest = @ToInterest,P.LateFee = @ToLateFee,P.NSFFee = 0,
	P.PrincipalPart = WF.PrincipalPart + WF.TranBalance,
	P.InterestPart = @ToInterest + WF.AddEscrowInterest,
	P.LateFeePart = CASE WHEN @LateFeePart > 0 THEN @LateFeePart ELSE WF.ToOwnedLateFee END,
	P.NSFFeePart = CASE WHEN @NSFFeePart > 0 THEN @NSFFeePart ELSE 0 END,
	P.PrincipalBalance = @PrincBal - (WF.PrincipalPart + WF.TranBalance),
	P.PaymentNumber = @PaymentNumber,P.ExpectedBalance = @ExpectedBalance,
	P.ExpectedPayment = @ExpectedPayment,P.DayCountConventionID = @DayCountConvention,P.IsProcessed = 1
FROM t_Payment P CROSS JOIN @WF WF
WHERE P.ID = @PaymentID

INSERT INTO v_PaymentTransaction#ForInsert(PaymentID,Interest,LateFee,NSF)
SELECT @PaymentID,WF.ToOwnedInterest,WF.ToOwnedLateFee,WF.ToOwnedNSF FROM @WF WF

INSERT INTO v_PaymentTransaction#ForInsert(PaymentID,Interest,LateFee,NSF)
SELECT @PaymentID,WF.AddEscrowInterest,WF.AddEscrowLateFee,WF.AddEscrowNSF FROM @WF WF

SELECT 'P'P,* FROM t_Payment WHERE LoanID = @LoanID-- ID = (SELECT ID FROM @O)
SELECT 'PT'PT,PT.* FROM v_PaymentTransaction PT WHERE PT.PaymentID = @PaymentID

WHILE EXISTS(SELECT * FROM t_Payment WHERE LoanID = @LoanID AND IsProcessed = 0) BEGIN
	SELECT TOP (1) @PaymentID = ID FROM t_Payment WHERE LoanID = @LoanID AND IsProcessed = 0 ORDER BY DatePaid,ID
	EXEC s_PaymentInsert @PaymentID
END

END
IF @StatusID = dbo.fs_StateID__Payments__NSF() BEGIN
	DECLARE @NSFFee numeric(9,2) SET @NSFFee = 25
	UPDATE t_Payment SET IsProcessed = 1,NSFFee = @NSFFee WHERE ID = @PaymentID

	INSERT INTO v_PaymentTransaction#ForInsert(PaymentID,Interest,LateFee,NSF)
	VALUES(@PaymentID,0,0,-@NSFFee)
END

--SELECT @PrincBal PrincBal,@DateNext DateNext,@DatePaid DatePaid,@PrevDatePaid PrevDatePaid,@ToInterest ToInterest,
--			 @NewDateNext NewDateNext,@ToLateFee LateCharges,@PaymentNumber Pmt#

